//
//  ICMessageDatabase.m
//  ICome
//
//  Created by ENN on 16/3/7.
//  Copyright © 2016年 iCom. All rights reserved.
//

#import "ICMessageDatabase.h"
#import "ICDatabaseManager.h"
#import "ICMessageModel.h"
#import "ICMessage.h"
#import "ICGroup.h"
#import "ICGroupUser.h"
#import "ICSearchMessage.h"
#import "ICMessageHelper.h"
#import "NSDate+Extension.h"
#import "ICNotify.h"

#import "ICGtasksModel.h"
#import "ICApplicationModel.h"
#import "ICSyncManager.h"

@implementation ICMessageDatabase

// save message
+ (BOOL)saveMessage:(ICMessageModel *)messageModel
{
    ICMessage *message = messageModel.message; // 消息类型要改变a

    int showState = message.deliveryState == ICMessageDeliveryState_Delivering ? 3 : message.deliveryState;
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"INSERT OR REPLACE INTO IC_Message(messageId,mfrom,mto,type,txt,fileKey,isSender,deliveryState,status,mediaPath,time,lnk)VALUES(%@,%@,%@,%@,%@,%@,%d,%d,%d,%@,%ld,%@);",message.messageId,message.from,message.to,message.type,message.content,message.fileKey,messageModel.isSender,showState,message.status,messageModel.mediaPath,message.date,message.lnk];
        if (!result) NSLog(@"save message error");
    }];
    
    
    // 正常保存最后一条消息到 消息组表中
    [ICMessageDatabase updateGroupLastMessageTime:message.to lastMsgTime:message.date lastMsgTxt:message.content lastMsgId:message.messageId lastChangeId:message.from];
    
    if (messageModel.message.status == ICMessageStatus_back) {
        [ICMessageDatabase messageBackAction:messageModel.message.to messageId:messageModel.message.messageId lastMsgTime:messageModel.message.date];
    }
    
    // 特殊业务需求 处理
    // 业务逻辑 处理  平台客服需 更新最后一条消息
    if (message.to != nil) {
        if ([message.to hasPrefix:@"rbt_"] && ![message.to isEqualToString:[NSString stringWithFormat:@"rbt_%@", [ICUser currentUser].eId]]) { //
            NSString *name = [ICMessageDatabase getGroupName:message.to];
            NSString *gName = name == nil ? message.to : name;
            NSString *serId = [NSString stringWithFormat:@"svr_%@", [ICUser currentUser].eId];
            NSString *lastMsgTxt = [NSString stringWithFormat:@"%@:%@", gName, message.content];
            [ICMessageDatabase updateGroupLastMessageTime:serId lastMsgTime:message.date lastMsgTxt:lastMsgTxt lastMsgId:message.messageId lastChangeId:message.from];
        }
    }
    return YES;
}

// send message success or fail to update message
+ (void)updateMessage:(ICMessage *)message identifier:(NSString *)identifier
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"UPDATE IC_Message SET messageId = %@, deliveryState = %d, time = %ld WHERE messageId = %@",message.messageId,message.deliveryState, message.date, identifier];
        if (!result) NSLog(@"updateMessage error");
    }];
}

// get messages of count
+ (void)selectMessageWithCount:(int)count
                         group:(NSString *)group
                  firstMessage:(ICMessage *)firstMessage
                        finish:(finishedBlock)finish
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet *set;
        if (firstMessage == nil) {  // 按时间降序取数据
            set = [db executeQueryWithFormat:@"SELECT * FROM IC_Message WHERE (mto = %@ AND (status = %d or status = %d)) ORDER BY time DESC LIMIT %d",group,ICMessageStatus_read,ICMessageStatus_unRead,count];
        } else {
            set = [db executeQueryWithFormat:@"SELECT * FROM IC_Message WHERE (mto = %@) AND (time < (select time from IC_Message WHERE messageId = %@ and mto = %@)) AND (status = %d or status = %d) ORDER BY time DESC LIMIT %d",group,firstMessage.messageId,group,ICMessageStatus_unRead,ICMessageStatus_read,count];
        }
        if (nil == set) {
            NSLog(@"no message");
            NSError *error = [NSError errorWithDomain:@"not load message" code:1005 userInfo:nil];
            finish(nil,error);
            return ;
        }
        NSInteger lastTime = 0;
        NSMutableArray *mutableArray = [NSMutableArray array];
        while ([set next]) {
            ICMessageFrame *messageF     = [[ICMessageFrame alloc] init];
            ICMessageModel *messageModel = [[ICMessageModel alloc] init];
            ICMessage *message           = [[ICMessage alloc] init];
            
            message.messageId = [set stringForColumn:@"messageId"];
            message.from      = [set stringForColumn:@"mfrom"];
            message.to        = [set stringForColumn:@"mto"];
            message.type      = [set stringForColumn:@"type"];
            message.content   = [set stringForColumn:@"txt"];
            message.fileKey   = [set stringForColumn:@"fileKey"];
            messageModel.isSender  = [set boolForColumn:@"isSender"];
            message.deliveryState  = [set intForColumn:@"deliveryState"];
            message.status    = [set boolForColumn:@"status"];
            messageModel.mediaPath = [set stringForColumn:@"mediaPath"];
            message.date           = [set longForColumn:@"time"];
            message.lnk            = [set stringForColumn:@"lnk"];
            
            messageModel.message         = message;
            messageF.model               = messageModel;
            
            NSDateFormatter *dateFormater = [[NSDateFormatter alloc] init];
            [dateFormater setDateFormat:@"yyyy-MM-dd"];
            if (lastTime > 0) {
                NSDate *date1 = [NSDate dateWithTimeIntervalSince1970:lastTime/1000];
                NSDate *date2 = [NSDate dateWithTimeIntervalSince1970:message.date/1000];
                BOOL isSame = [NSDate isSameDay:date1 date2:date2];
                if (!isSame) {
                    NSString *datestr = [dateFormater stringFromDate:date1];
                    ICMessageFrame *timeF = [ICMessageHelper createTimeMessageFrame:TypeSystem
                                                                            content:datestr
                                                                               path:nil
                                                                               from:nil
                                                                                 to:message.to
                                                                            fileKey:nil
                                                                           isSender:NO
                                                           receivedSenderByYourself:NO];
                    [mutableArray addObject:timeF];
                }
            }
            lastTime = message.date;
            
            [mutableArray addObject:messageF];
            
            if ([message.messageId isEqualToString:@"1"]) {// 创建的时间
                NSString *datestr = [dateFormater stringFromDate:[NSDate dateWithTimeIntervalSince1970:message.date/1000]];
                ICMessageFrame *timeF = [ICMessageHelper createTimeMessageFrame:TypeSystem
                                                                        content:datestr
                                                                           path:nil
                                                                           from:nil
                                                                             to:message.to
                                                                        fileKey:nil
                                                                       isSender:NO
                                                       receivedSenderByYourself:NO];
                [mutableArray addObject:timeF];
            }
        }
        if (mutableArray.count > 0) { // 有数据
            NSArray *reverseArry = [[mutableArray reverseObjectEnumerator] allObjects];
            finish(reverseArry,nil);
        } else {    // 没加载到数据
            finish(@[],nil);
        }
        
    }];
}

// get messages of count
+ (void)selectASCMessageWithCount:(int)count
                            group:(NSString *)group
                     firstMessage:(ICMessage *)firstMessage
                           finish:(finishedBlock)finish
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet *set;
        if (firstMessage == nil) {  // 按时间降序取数据
            set = [db executeQueryWithFormat:@"SELECT * FROM IC_Message WHERE (mto = %@ AND (status = %d or status = %d)) ORDER BY time DESC LIMIT %d",group,ICMessageStatus_read,ICMessageStatus_unRead,count];
        } else {
            set = [db executeQueryWithFormat:@"SELECT * FROM IC_Message WHERE (mto = %@) AND (time < (select time from IC_Message WHERE messageId = %@ and mto = %@)) AND (status = %d or status = %d) ORDER BY time DESC LIMIT %d",group,firstMessage.messageId,group,ICMessageStatus_unRead,ICMessageStatus_read,count];
        }
        if (nil == set) {
            NSLog(@"no message");
            NSError *error = [NSError errorWithDomain:@"not load message" code:1005 userInfo:nil];
            finish(nil,error);
            return ;
        }
        NSInteger lastTime = 0;
        NSMutableArray *mutableArray = [NSMutableArray array];
        while ([set next]) {
            ICMessageFrame *messageF     = [[ICMessageFrame alloc] init];
            ICMessageModel *messageModel = [[ICMessageModel alloc] init];
            ICMessage *message           = [[ICMessage alloc] init];
            
            message.messageId = [set stringForColumn:@"messageId"];
            message.from      = [set stringForColumn:@"mfrom"];
            message.to        = [set stringForColumn:@"mto"];
            message.type      = [set stringForColumn:@"type"];
            message.content   = [set stringForColumn:@"txt"];
            message.fileKey   = [set stringForColumn:@"fileKey"];
            messageModel.isSender  = [set boolForColumn:@"isSender"];
            message.deliveryState  = [set intForColumn:@"deliveryState"];
            message.status    = [set boolForColumn:@"status"];
            messageModel.mediaPath = [set stringForColumn:@"mediaPath"];
            message.date           = [set longForColumn:@"time"];
            message.lnk            = [set stringForColumn:@"lnk"];
            
            messageModel.message         = message;
            messageF.model               = messageModel;
            
            NSDateFormatter *dateFormater = [[NSDateFormatter alloc] init];
            [dateFormater setDateFormat:@"yyyy-MM-dd"];
//            NSDate *date1 = [NSDate dateWithTimeIntervalSince1970:lastTime/1000];
            NSDate *date2 = [NSDate dateWithTimeIntervalSince1970:message.date/1000];
//            BOOL isSame = [NSDate isSameDay:date1 date2:date2];
//            if (!isSame) {
                NSString *datestr = [ICMessageHelper  timeFormatWithDate2:[NSDate longFromDate:date2]];
                ICMessageFrame *timeF = [ICMessageHelper createTimeMessageFrame:TypeSystem
                                                                        content:datestr
                                                                           path:nil
                                                                           from:nil
                                                                             to:message.to
                                                                        fileKey:nil
                                                                       isSender:NO
                                                       receivedSenderByYourself:NO];
                [mutableArray addObject:timeF];
//            }
            lastTime = message.date;
            
            [mutableArray addObject:messageF];
            
            if ([message.messageId isEqualToString:@"1"]) {// 创建的时间
                NSString *datestr = [ICMessageHelper  timeFormatWithDate2:message.date];
                ICMessageFrame *timeF = [ICMessageHelper createTimeMessageFrame:TypeSystem
                                                                        content:datestr
                                                                           path:nil
                                                                           from:nil
                                                                             to:message.to
                                                                        fileKey:nil
                                                                       isSender:NO
                                                       receivedSenderByYourself:NO];
                [mutableArray addObject:timeF];
            }
        }
        if (mutableArray.count > 0) { // 有数据
            NSArray *reverseArry = [[mutableArray reverseObjectEnumerator] allObjects];
            finish(reverseArry,nil);
        } else {    // 没加载到数据
            finish(nil,nil);
        }
        
    }];
}




+ (ICMessageFrame *)getMessage:(NSString *)gId
                         msgId:(NSString *)messageId
{
    __block ICMessageFrame *messageF     = [[ICMessageFrame alloc] init];
    __block ICMessageModel *messageModel = [[ICMessageModel alloc] init];
    __block ICMessage *message           = [[ICMessage alloc] init];
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet *set = [db executeQueryWithFormat:@"SELECT * FROM IC_Message WHERE mto = %@ AND messageId = %@;",gId,messageId];
        while ([set next]) {
            message.messageId = [set stringForColumn:@"messageId"];
            message.from      = [set stringForColumn:@"mfrom"];
            message.to        = [set stringForColumn:@"mto"];
            message.type      = [set stringForColumn:@"type"];
            message.content   = [set stringForColumn:@"txt"];
            message.fileKey   = [set stringForColumn:@"fileKey"];
            messageModel.isSender  = [set boolForColumn:@"isSender"];
            message.deliveryState  = [set intForColumn:@"deliveryState"];
            message.status    = [set intForColumn:@"status"];
            messageModel.mediaPath = [set stringForColumn:@"mediaPath"];
            message.date           = [set longForColumn:@"time"];
            messageModel.message         = message;
            messageF.model               = messageModel;
        }
    }];
    return messageF;
}

// save group
+ (void)saveGroup:(ICGroup *)group
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"INSERT OR REPLACE INTO IC_Group(gId,gName,photoId,gType,master,unreadCount,valid,busy,isTop,lastMsgTime,localType,userId)VALUES(%@,%@,%@,%@,%d,%d,%d,%d,%d,%ld,%d,%@);",group.gId,group.gName,group.photoId,group.gType,group.master,group.unReadCount,group.valid,group.busy,0,group.lastMsgTime,1,[ICUser currentUser].eId];
        if (!result) NSLog(@"save group error");
    }];
    [self saveGroupUser:group];
}

+ (void)saveGroup:(ICGroup *)group isTop:(BOOL)isTop localType:(int)localType
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"INSERT OR REPLACE INTO IC_Group(gId,gName,photoId,gType,master,unreadCount,valid,busy,isTop,lastMsgTime,localType,lastMsgTxt,userId)VALUES(%@,%@,%@,%@,%d,%d,%d,%d,%d,%ld,%d,%@,%@);",group.gId,group.gName,group.photoId,group.gType,group.master,group.unReadCount,group.valid,group.busy,isTop,group.lastMsgTime,localType,group.lastMsgString,[ICUser currentUser].eId];
        if (!result) NSLog(@"save group error");
    }];
    [self saveGroupUser:group];
}

+ (void)saveGroupUser:(ICGroup *)group
{
    if (group.userList.count == 0) return ;
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        for (ICUser *user in group.userList) {
            BOOL result = [db executeUpdateWithFormat:@"INSERT OR REPLACE INTO ic_group_employee(gId,eId,nName)VALUES(%@,%@,%@);",group.gId,user.eId,user.nName];
            if (!result) NSLog(@"save groupUser error");
        }
    }];
}

+ (void)updateGroupInfo:(ICGroup *)group
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"UPDATE IC_Group SET photoId = %@,gName = %@,valid = %d,gType = %@,busy = %d,master = %d WHERE gId = %@;",group.photoId,group.gName,group.valid,group.gType,group.busy,group.master,group.gId];
        if (!result) NSLog(@"updateGroupInfo error");
    }];
}

// 更改用户信息
+ (void)updateUserPhotoId:(NSString *)photoId
                      eId:(NSString *)eId
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"UPDATE ic_employee SET photoId = %@ WHERE eId = %@;",photoId,eId];
        if (!result) NSLog(@"updateUserPhotoId error");
    }];
}

+ (void)selectGroupsIsTop:(BOOL)isTop
                    gType:(NSString *)gType
                   finish:(finishedBlock)finish
{
    
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet * set ;
        if (isTop) { // 置顶
            set =  [db executeQueryWithFormat:@"SELECT * FROM IC_Group WHERE isTop = %d AND localType = %d ORDER BY topTime DESC;",isTop,1];
        } else { // 非置顶
            if (![ICTools stringEmpty:gType]) { // 按类型获取
                set =  [db executeQueryWithFormat:@"SELECT * FROM IC_Group WHERE isTop = %d AND localType = %d AND gType = %@  ORDER BY lastMsgTime DESC;",isTop,1,gType];
            } else {
                set =  [db executeQueryWithFormat:@"SELECT * FROM IC_Group WHERE isTop = %d AND localType = %d ORDER BY lastMsgTime DESC;",isTop,1];
            }
        }
        
        NSMutableArray *mutableArr = [NSMutableArray array];
        NSString *eId = nil;
        NSString *eName = nil;
        NSString *draft = nil;
        while ([set next]) {
            ICGroup *group   = [[ICGroup alloc] init];
            group.gId        = [set stringForColumn:@"gId"];
            group.gName      = [set stringForColumn:@"gName"];
            group.gType      = [set stringForColumn:@"gType"];
            group.master     = [set intForColumn:@"master"];
            group.unReadCount   = [set intForColumn:@"unreadCount"];
            group.isTop         = [set intForColumn:@"isTop"];
            group.lastMsgTime = [set longForColumn:@"lastMsgTime"];
            group.photoId     = [set stringForColumn:@"photoId"];
            group.valid       = [set intForColumn:@"valid"];
            group.lastMsgString = [set stringForColumn:@"lastMsgTxt"];
            group.lastMsgId = [set stringForColumn:@"lastMsgId"];
            group.lastChangeGroupUserId = [set stringForColumn:@"userId"];
            group.status = [set intForColumn:@"status"];
            group.busy = [set intForColumn:@"busy"];
            // 先去拿草稿
            if (group.valid == 1) {
                FMResultSet *dSet = [db executeQueryWithFormat:@"SELECT draft FROM IC_Drafts WHERE gId = %@;",group.gId];
                while ([dSet next]) {
                    draft = [dSet stringForColumn:@"draft"];
                }
            }
            if (draft == nil || group.status == 1) { // 没草稿 或者有@消息类型
                
                //由于设计缺陷 ic_group表lastMsgString为空时从IC_Message中取lastMsgString
                if ([ICTools stringEmpty:group.lastMsgString]) {
                    // 从message表中拿最后一条消息
                    FMResultSet *mSet = [db executeQueryWithFormat:@"SELECT * FROM IC_Message WHERE mto = %@ AND ( status = 0 OR status = 1 )ORDER BY CAST(messageId AS INTEGER) DESC LIMIT 1;",group.gId];
                    while ([mSet next]) {
                        group.lastMsgString = [mSet stringForColumn:@"txt"];
                        eId = [mSet stringForColumn:@"mfrom"];
                    }
                } else {
                    eId = group.lastChangeGroupUserId;
                }
                
                if ([group.gType isEqualToString:@"2"]) {
                    if (eId && ![eId isEqualToString:[ICUser currentUser].eId]) {
                        FMResultSet *fSet = [db executeQueryWithFormat:@"SELECT * FROM ic_group_employee WHERE eId = %@ AND gId = %@;",eId,group.gId];
                        while ([fSet next]) {
                            eName = [fSet stringForColumn:@"nName"];
                        }
                        if (eName) {
                            NSString *lastMsgString = @"";
                            if (![ICTools stringEmpty:group.lastMsgString]) {
                                group.lastMsgTxt = group.lastMsgString;
                                lastMsgString = [NSString stringWithFormat:@"%@: %@",eName,group.lastMsgString];
                            }
                            group.lastMsgString = lastMsgString;
                            group.lastMsgeId = eId;
                            group.lastMsgeName = eName;
                        }
                    }
                } else if ([group.gType isEqualToString:@"1"]) { // 双人组
                    if (eId && ![eId isEqualToString:[ICUser currentUser].eId]) {
                        FMResultSet *fSet = [db executeQueryWithFormat:@"SELECT * FROM ic_group_employee WHERE eId = %@ AND gId = %@;",eId,group.gId];
                        while ([fSet next]) {
                            eName = [fSet stringForColumn:@"nName"];
                        }
                        if (eName) {
                            if (![ICTools stringEmpty:group.lastMsgString]) {
                                group.lastMsgTxt = group.lastMsgString;
                            }
                            group.lastMsgeId = eId;
                            group.lastMsgeName = eName;
                        }
                    }
                }
                
            } else { // 有草稿
                group.lastMsgString = draft;
                group.isDraft       = YES;
                draft = nil;  // 清空
            }
            [mutableArr addObject:group];
        }
        finish(mutableArr,nil);
    }];
}

// search all group
+ (void)searchGroupWithSearchText:(NSString *)text
                           finish:(finishedBlock)finish
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        NSString *sql=[NSString stringWithFormat:@"SELECT * FROM IC_Group WHERE localType = %d AND gName like '%%%@%%' ORDER BY lastMsgTime DESC;",1,text];
        FMResultSet *set = [db executeQuery:sql];
        NSMutableArray *mutableArr = [NSMutableArray array];
        while ([set next]) {
            ICGroup *group   = [[ICGroup alloc] init];
            group.gId        = [set stringForColumn:@"gId"];
            group.gName      = [set stringForColumn:@"gName"];
            group.gType      = [set stringForColumn:@"gType"];
            group.master     = [set intForColumn:@"master"];
            group.unReadCount   = [set intForColumn:@"unreadCount"];
            group.isTop         = [set intForColumn:@"isTop"];
            group.lastMsgTime = [set longForColumn:@"lastMsgTime"];
            group.photoId     = [set stringForColumn:@"photoId"];
            group.lastMsgString = [set stringForColumn:@"lastMsgTxt"];
            group.lastMsgId = [set stringForColumn:@"lastMsgId"];
            group.valid       = [set intForColumn:@"valid"];
            //            // 从message表中拿最后一条消息
            //            FMResultSet *mSet = [db executeQueryWithFormat:@"SELECT * FROM IC_Message WHERE mto = %@ ORDER BY CAST(messageId AS INTEGER) DESC LIMIT 1;",group.gId];
            //            while ([mSet next]) {
            //                group.lastMsgString = [mSet stringForColumn:@"txt"];
            //            }
            [mutableArr addObject:group];
        }
        finish(mutableArr,nil);
    }];
}




// get unread message count
+ (int)getGroupUnreadMessageCountWithGroupId:(NSString *)gId;
{
    __block int count = 0;
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet *set = [db executeQueryWithFormat:@"SELECT * FROM IC_Group WHERE gId = %@;",gId];
        while ([set next]) {
            count = [set intForColumn:@"unreadCount"];
        }
    }];
    return count;
}


+ (void)getGroups:(finishedBlock)finish
{
    
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet * set = [db executeQueryWithFormat:@"SELECT * FROM IC_Group WHERE localType = %d ORDER BY lastMsgTime DESC;",1];
        if (nil == set) {
            NSLog(@"no group");
            finish(nil,nil);
            return ;
        }
        NSMutableArray *mutableArr = [NSMutableArray array];
        while ([set next]) {
            ICGroup *group   = [[ICGroup alloc] init];
            group.gId        = [set stringForColumn:@"gId"];
            group.gName      = [set stringForColumn:@"gName"];
            group.photoId     = [set stringForColumn:@"photoId"];
            group.gType     = [set stringForColumn:@"gType"];
            group.valid       = [set intForColumn:@"valid"];
            group.unReadCount = [set intForColumn:@"unreadCount"];
            [mutableArr addObject:group];
        }
        finish(mutableArr,nil);
    }];
}

+ (void)getNormalGroups:(finishedBlock)finish
{
    
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet * set = [db executeQueryWithFormat:@"SELECT * FROM IC_Group WHERE localType = %d AND (gType = '0' OR gType = '1' OR gType = '2')  ORDER BY lastMsgTime DESC;",1];
        if (nil == set) {
            NSLog(@"no group");
            finish(nil,nil);
            return ;
        }
        NSMutableArray *mutableArr = [NSMutableArray array];
        while ([set next]) {
            ICGroup *group   = [[ICGroup alloc] init];
            group.gId        = [set stringForColumn:@"gId"];
            group.gName      = [set stringForColumn:@"gName"];
            group.photoId     = [set stringForColumn:@"photoId"];
            group.gType     = [set stringForColumn:@"gType"];
            group.valid       = [set intForColumn:@"valid"];
            [mutableArr addObject:group];
        }
        finish(mutableArr,nil);
    }];
}
// 后续获取群组统一使用该方法
+ (void)getGroupsWithgType:(ICGroupType)gType Finish:(finishedBlock)finish
{
    
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet * set = [db executeQueryWithFormat:@"SELECT * FROM IC_Group WHERE localType = %d AND gType = %@  ORDER BY lastMsgTime DESC;",1, [NSString stringWithFormat:@"%d",gType]];
        if (nil == set) {
            NSLog(@"no group");
            finish(nil,nil);
            return ;
        }
        NSMutableArray *mutableArr = [NSMutableArray array];
        while ([set next]) {
            ICGroup *group   = [[ICGroup alloc] init];
            group.gId        = [set stringForColumn:@"gId"];
            group.gName      = [set stringForColumn:@"gName"];
            group.photoId     = [set stringForColumn:@"photoId"];
            group.gType     = [set stringForColumn:@"gType"];
            group.valid       = [set intForColumn:@"valid"];
            [mutableArr addObject:group];
        }
        finish(mutableArr,nil);
    }];
}



+ (void)updateGroupUnreadMessageCountWithGroupId:(NSString *)gId
                                           count:(int)count
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"UPDATE IC_Group SET unreadCount = %d WHERE gId = %@;",count,gId];
        if (!result) NSLog(@"updateGroupUnreadMessageCount error");
    }];
}

+ (void)updateGroupAlertStatus:(NSInteger)status gId:(NSString *)gId;
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"UPDATE IC_Group SET status = %ld WHERE gId = %@;",(long)status,gId];
        if (!result) NSLog(@"updateGroupStatus error");
    }];
}

// save user
+ (void)saveUser:(ICUser *)user
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"INSERT OR REPLACE INTO ic_employee (eId,eName,photoId,email) VALUES (%@,%@,%@,%@);",user.eId,user.eName,user.photoId,user.email];
        if (!result) NSLog(@"%@", [NSString stringWithFormat:@"saveUser error %@ -- %@", user.eId, user.eName]);
    }];
}
// 批量保存用户
+ (void)saveUsers:(NSArray *)users {
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        for (ICUser *user in users) {
            BOOL result = [db executeUpdateWithFormat:@"INSERT OR REPLACE INTO ic_employee (eId,eName,photoId,email) VALUES (%@,%@,%@,%@);",user.eId,user.eName,user.photoId,user.email];
//            NSLog(@"%d", result);
            if (!result) NSLog(@"%@", [NSString stringWithFormat:@"saveUser error"]);
        }
    }];
}

// get userName
+ (NSString *)userNameWitheId:(NSString *)eId
{
    __block NSString *eName = nil;
    @try {
        [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
            FMResultSet *set = [db executeQueryWithFormat:@"SELECT * FROM ic_employee WHERE eId = %@;",eId];
            while ([set next]) {
                eName = [set stringForColumn:@"eName"];
            }
        }];
    } @catch (NSException *exception) {
        NSLog(@"error reason %@", exception.reason);
        return @"";
    } @finally {
        
    }
    return eName == nil ? @"":eName;
}

+ (NSString *)usergIdWitheId:(NSString *)eId {
    
    __block NSString *gId = [[NSString alloc] init];
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet *set = [db executeQueryWithFormat:@"SELECT * FROM ic_group_employee WHERE eId = %@ AND gId like 'dbl%%';",eId];
        while ([set next]) {
            gId = [set stringForColumn:@"gId"];
        }
    }];
    return gId;
}

+ (void)saveGroupUserWithGroupId:(NSString *)gId
                       groupUser:(ICGroupUser *)groupUser
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"INSERT OR REPLACE INTO ic_group_employee(gId,eId,valid,nName)VALUES(%@,%@,%d,%@);",gId,groupUser.eId,groupUser.valid,groupUser.nName];
        if (!result) NSLog(@"saveGroupUserWithGroupId error");
    }];
}

+ (void)saveGroupUsersWithGroupId:(NSString *)gId
                       groupUsers:(NSArray *)groupUsers {
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        for (ICGroupUser *groupUser in groupUsers) {
            BOOL result = [db executeUpdateWithFormat:@"INSERT OR REPLACE INTO ic_group_employee (gId,eId,valid,nName) VALUES (%@,%@,%d,%@);",gId,groupUser.eId,groupUser.valid, groupUser.nName == nil ? @"":groupUser.nName];
            if (!result) NSLog(@"saveGroupUserWithGroupId error");
        }
    }];
}


// 根据gId删除某条关联关系
+ (void)deleteGroupUserWithGroupId:(NSString *)gId
                               eId:(NSString *)eId
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"DELETE FROM ic_group_employee WHERE gId = %@ AND eId = %@;",gId,eId];
        if (!result) NSLog(@"deleteGroupUserWithGroupId error");
    }];
}
// top group
+ (void)makeGroupTop:(ICGroup *)group
               isTop:(BOOL)isTop
         lastMsgTime:(NSInteger)lastMsgTime
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"UPDATE IC_Group SET isTop = %d,topTime = %ld WHERE gId = %@;",isTop,lastMsgTime,group.gId];
        if (!result) NSLog(@"makeGroupTop error");
    }];
}

// delete group
+ (void)deleteGroupWithGroupId:(NSString *)gId
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"DELETE FROM IC_Group WHERE gId = %@;",gId];
        if (!result) NSLog(@"deleteGroupWithGroupId error");
    }];
}
#warning ----- SELECT ic_employee.eId,ic_employee.eName,ic_employee.photoId
// 最近联系人
+ (void)recentContactUsers:(finishedBlock)finish
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        NSMutableArray *mutableArr = [NSMutableArray array];
        //        FMResultSet *mset = [db executeQueryWithFormat:@"SELECT ic_employee.eId,ic_employee.eName,ic_employee.photoId FROM IC_Group,ic_group_employee,ic_employee WHERE IC_Group.gType = 1 AND IC_Group.gId = ic_group_employee.gId AND ic_group_employee.eId = ic_employee.eId;"];
        // LEFT JOIN ON 左查询
        FMResultSet *mset = [db executeQueryWithFormat:@"SELECT c.eId, c.eName, c.photoId FROM ic_group_employee a LEFT JOIN IC_Group b on a.gId = b.gId LEFT JOIN ic_employee c ON c.eId = a.eId WHERE b.gType = 1 AND b.localType = 1;"];
        
        while ([mset next]) {
            ICUser *user   = [[ICUser alloc] init];
            user.eId       = [mset stringForColumn:@"eId"];
            user.eName     = [mset stringForColumn:@"eName"];
            user.photoId   = [mset stringForColumn:@"photoId"];
            if (![[ICUser currentUser].eId isEqualToString:user.eId] && ![ICTools stringEmpty:user.eId]) {
                [mutableArr addObject:user];
            }
        }
        NSArray *reverseArr = [[mutableArr reverseObjectEnumerator] allObjects];
        finish(reverseArr,nil);
    }];
}


// delete message
+ (void)deleteMessage:(ICMessage *)message messageId:(NSString *)messageId
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"DELETE FROM IC_Message WHERE messageId = %@;",messageId];
        if (!result) NSLog(@"deleteMessage error");
    }];
}

// change message status
+ (void)changeMessageStatus:(ICMessage *)message
                     status:(ICMessageStatus)messageStatus
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"UPDATE IC_Message SET status = %d WHERE messageId = %@;",messageStatus,message.messageId];
        if (!result) NSLog(@"changeMessageStatus error");
    }];
}

// get userList of group
+ (void)getUsersWithGroupId:(NSString *)gId
                     finish:(finishedBlock)finish
{
    
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        
        FMResultSet *set = [db executeQueryWithFormat:@"SELECT ic_employee.eId,ic_employee.eName,ic_employee.photoId,ic_group_employee.nName, ic_group_employee.id FROM ic_group_employee,ic_employee WHERE gId = %@ AND ic_group_employee.eId = ic_employee.eId ORDER BY ic_group_employee.id ASC;",gId];
        NSMutableArray *mutableArr = [NSMutableArray array];
        while ([set next]) {
            ICUser *user   = [[ICUser alloc] init];
            user.eId       = [set stringForColumn:@"eId"];
            user.eName     = [set stringForColumn:@"nName"];
            if (user.eName == nil) {
                user.eName = [set stringForColumn:@"eName"];
            }
//            user.email     = [set stringForColumn:@"email"];
            user.photoId   = [set stringForColumn:@"photoId"];
            [mutableArr addObject:user];
        }
        finish(mutableArr,nil);
    }];
}

// get userList of group
+ (void)getUsersWithGroupId:(NSString *)gId
                      limit:(int)limit
                     finish:(finishedBlock)finish
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        
        FMResultSet *set = [db executeQueryWithFormat:@"SELECT ic_employee.eId,ic_employee.eName,ic_employee.photoId,ic_group_employee.nName, ic_group_employee.id FROM ic_group_employee,ic_employee WHERE gId = %@ AND ic_group_employee.eId = ic_employee.eId ORDER BY ic_group_employee.id ASC  LIMIT %d;",gId, limit];
        NSMutableArray *mutableArr = [NSMutableArray array];
        while ([set next]) {
            ICUser *user   = [[ICUser alloc] init];
            user.eId       = [set stringForColumn:@"eId"];
            user.eName     = [set stringForColumn:@"nName"];
            if (user.eName == nil) {
                user.eName = [set stringForColumn:@"eName"];
            }
            user.photoId   = [set stringForColumn:@"photoId"];
            [mutableArr addObject:user];
        }
        finish(mutableArr,nil);
    }];
}

+ (void)getUsersOfGroupId:(NSString *)gId
                searchTxt:(NSString *)text
                   finish:(finishedBlock)finish
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        NSString *sql = [NSString stringWithFormat:@"SELECT ic_employee.eId,eName FROM ic_group_employee,ic_employee WHERE gId = '%@' AND ic_group_employee.eId = ic_employee.eId AND eName like '%%%@%%';",gId,text];
        FMResultSet *set = [db executeQuery:sql];
        NSMutableArray *mutableArr = [NSMutableArray array];
        while ([set next]) {
            ICUser *user   = [[ICUser alloc] init];
            user.eId       = [set stringForColumn:@"eId"];
            user.eName     = [set stringForColumn:@"eName"];
            [mutableArr addObject:user];
        }
        finish(mutableArr,nil);
    }];
}

+ (void)searchUsersWithGroupId:(NSString *)gId
                     searchTxt:(NSString *)text
                        finish:(finishedBlock)finish
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        NSString *sql = [NSString stringWithFormat:@"SELECT ic_group_employee.eId,nName,photoId FROM ic_group_employee,ic_employee WHERE gId = '%@' AND ic_group_employee.eId = ic_employee.eId AND nName like '%%%@%%';",gId,text];
        FMResultSet *set = [db executeQuery:sql];
        NSMutableArray *mutableArr = [NSMutableArray array];
        while ([set next]) {
            ICUser *user = [[ICUser alloc] init];
            user.eId     = [set stringForColumn:@"eId"];
            user.eName   = [set stringForColumn:@"nName"];
            user.photoId = [set stringForColumn:@"photoId"];
            user.nName   = user.eName;
            [mutableArr addObject:user];
        }
        finish(mutableArr,nil);
    }];
}




+ (void)getSearchGroups:(NSString *)gId
              searchTxt:(NSString *)text
                 finish:(finishedBlock)finish
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        NSString *sql = [NSString stringWithFormat:@"SELECT * FROM IC_Group WHERE gName like '%%%@%%';",text];
        FMResultSet *set = [db executeQuery:sql];
        NSMutableArray *mutableArr = [NSMutableArray array];
        while ([set next]) {
            ICGroup *group = [[ICGroup alloc] init];
            group.gId      = [set stringForColumn:@"gId"];
            group.gName    = [set stringForColumn:@"gName"];
            group.photoId  = [set stringForColumn:@"photoId"];
            group.valid       = [set intForColumn:@"valid"];
            [mutableArr addObject:group];
        }
        finish(mutableArr,nil);
    }];
}

+ (void)getGroupWithgId:(NSString *)gId
                 finish:(finishedBlock)finish
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        NSString *sql = [NSString stringWithFormat:@"SELECT * FROM IC_Group WHERE gId = '%@';",gId];
        FMResultSet *set = [db executeQuery:sql];
        NSMutableArray *mutableArr = [NSMutableArray array];
        while ([set next]) {
            ICGroup *group = [[ICGroup alloc] init];
            group.gId      = [set stringForColumn:@"gId"];
            group.gName    = [set stringForColumn:@"gName"];
            group.photoId  = [set stringForColumn:@"photoId"];
            group.valid       = [set intForColumn:@"valid"];
            [mutableArr addObject:group];
        }
        finish(mutableArr,nil);
    }];
}


// update group name
+ (void)updateGroupNameWithGroupId:(NSString *)gId
                             gName:(NSString *)gName
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"UPDATE IC_Group SET gName = %@ WHERE gId = %@",gName,gId];
        if (!result) NSLog(@"updateGroupNameWithGroupId error");
    }];
}

// 群信息同步
+ (void)updateGroupSyn:(NSString *)gId
                 gName:(NSString *)gName
               photoId:(NSString *)photoId
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"UPDATE IC_Group SET gName = %@,photoId = %@ WHERE gId = %@;",gName,photoId,gId];
        if (!result) NSLog(@"updateGroupSyn error");
    }];
}

// 获取 group's photoId
+ (NSString *)getGroupPhotoIdWithGroupId:(NSString *)gId
{
    __block NSString *photoId = [[NSString alloc] init];
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet *set = [db executeQueryWithFormat:@"SELECT * FROM IC_Group WHERE gId = %@;",gId];
        while ([set next]) {
            photoId = [set stringForColumn:@"photoId"];
        }
    }];
    return photoId;
}

#pragma 错误次数较多  -- 优化处理
// 取得用户名
+ (NSString *)getUserNameWithEId:(NSString *)eId
{
    if ([ICTools stringEmpty:eId])return @"";
    __block NSString *eName = [[NSString alloc] init];
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet *set = [db executeQueryWithFormat:@"SELECT * FROM ic_employee WHERE eId = %@;",eId];
        while ([set next]) {
            eName = [set stringForColumn:@"eName"];
            break;
        }
    }];
    return eName;
}

+ (NSString *)getPhotoIdWithEId:(NSString *)eId
{
    __block NSString *photoId = [[NSString alloc] init];
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet *set = [db executeQueryWithFormat:@"SELECT * FROM ic_employee WHERE eId = %@;",eId];
        while ([set next]) {
            photoId = [set stringForColumn:@"photoId"];
        }
    }];
    return photoId;
}

+ (NSString *)getUsersWithName:(NSString *)name gId:(NSString *)gId {
   __block NSString *eId = @"";
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet *set = [db executeQueryWithFormat:@"SELECT ic_employee.eId FROM ic_group_employee,ic_employee WHERE gId = %@ AND ic_group_employee.eId = ic_employee.eId and (ic_group_employee.nName = %@ or ic_employee.eName = %@) limit 1;",gId, name, name];
        while ([set next]) {
            eId = [set stringForColumn:@"eId"];
        }
    }];
    return eId;
}

// 得到昵称
+ (NSString *)nickNameWithEid:(NSString *)eId gId:(NSString *)gId
{
    __block NSString *nName = [[NSString alloc] init];
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet *set = [db executeQueryWithFormat:@"SELECT nName FROM ic_group_employee WHERE eId = %@ AND gId = %@;",eId,gId];
        while ([set next]) {
            nName = [set stringForColumn:@"nName"];
        }
    }];
    return nName.length > 0 ? nName : nil;
}


+ (void)updateNickNameWith:(NSString *)eId
                       gId:(NSString *)gId
                     nName:(NSString *)nName
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"UPDATE ic_group_employee SET nName = %@ WHERE gId = %@ AND eId = %@;",nName,gId,eId];
        if (!result) NSLog(@"updateNickName error");
    }];
}

+ (BOOL)isTopWithGroupId:(NSString *)gId
{
    __block BOOL isTop = NO;
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet *set = [db executeQueryWithFormat:@"SELECT * FROM IC_Group WHERE gId = %@;",gId];
        while ([set next]) {
            isTop  = [set intForColumn:@"isTop"];
        }
    }];
    return isTop;
}

+  (BOOL)isMasterWithGroupId:(NSString *)gId;
{
    __block BOOL isMaster = NO;
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet *set  = [db executeQueryWithFormat:@"SELECT * FROM IC_Group WHERE gId = %@;",gId];
        while ([set next]) {
            isMaster      = [set intForColumn:@"master"];
        }
    }];
    return isMaster;
}

+ (void)changeGroupMaster:(NSString *)gId
                 isMaster:(BOOL)isMaster
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"UPDATE IC_Group SET master =  %d WHERE gId = %@;",isMaster,gId];
        if (!result) NSLog(@"changeGroupMaster error");
    }];
}

// 更改自身对应群的消息免打扰状态
+ (void)changeGroupBusy:(NSString *)gId
                 isBusy:(BOOL)isBusy
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"UPDATE IC_Group SET busy =  %d WHERE gId = %@;",isBusy,gId];
        if (!result) NSLog(@"changeGroupBusy error");
    }];
}

// busy status
+ (BOOL)isBusyWithGroupId:(NSString *)gId
{
    __block BOOL isBusy = NO;
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet *set = [db executeQueryWithFormat:@"SELECT * FROM IC_Group WHERE gId = %@;",gId];
        while ([set next]) {
            isBusy  = [set intForColumn:@"busy"];
        }
    }];
    return isBusy;
}
#warning  - 删除消息 对消息进行update hide 操作，不是进行delete操作 - 改（相应的需要改表结构）
+ (void)clearChatRecord:(NSString *)gId
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"DELETE FROM IC_Message WHERE mto = %@;",gId];
        if (!result) NSLog(@"clearChatRecord error");
    }];
}

+ (void)invalidGroup:(NSString *)gId
               valid:(BOOL)valid
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"UPDATE IC_Group SET valid =  %d WHERE gId = %@;",valid,gId];
        if (!result) NSLog(@"invalidGroup error");
    }];
}

// group valid status for current user
+ (BOOL)isValidWithGroup:(NSString *)gId
{
    __block BOOL isValid = YES; // 有效
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet *set = [db executeQueryWithFormat:@"SELECT * FROM IC_Group WHERE gId = %@;",gId];
        while ([set next]) {
            isValid      = [set intForColumn:@"valid"];
        }
    }];
    return isValid;
}

+ (BOOL)isMessageExisted:(NSString *)gId
               messageId:(NSString *)messageId
{
    __block BOOL isExisted = NO;
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet *set   = [db executeQueryWithFormat:@"SELECT COUNT(*) FROM IC_Message WHERE mto = %@ AND messageId = %@;",gId,messageId];
        //        FMResultSet *set   = [db executeQueryWithFormat:@"SELECT COUNT(*) FROM IC_Group WHERE gId = %@ AND lastMsgTime = %@;",gId,lastMsgTime];
        while ([set next]) {
            int count      = [set intForColumnIndex:0];
            if (count > 0) {
                isExisted  = YES;
            } else {
                isExisted  = NO;
            }
        }
    }];
    return isExisted;
}
//            group.lastMsgId = [set stringForColumn:@"lastMsgId"];
+ (void)updateGroupLastMessageTime:(NSString *)gId
                       lastMsgTime:(NSInteger)lastMsgTime
                        lastMsgTxt:(NSString *)lastMsgTxt
                         lastMsgId:(NSString *)lastMsgId
                      lastChangeId:(NSString *)changeId
{
    NSMutableArray *reguleStrArray = [lastMsgTxt regularExpressionStr];  // eId
    __block NSString *reguleStr = nil;
    if ([reguleStrArray count] == 0) {
        reguleStr = lastMsgTxt;
    } else { // 需要进行替换
        reguleStr = lastMsgTxt;
        [reguleStrArray enumerateObjectsUsingBlock:^(id  _Nonnull obj, NSUInteger idx, BOOL * _Nonnull stop) {
            NSString *nName = [ICMessageDatabase nickNameWithEid:obj gId:gId];
            NSString *orgstr = [NSString stringWithFormat:@"[(eId=%@)]",obj];
            if (nName == nil){ // 去拿eName
                nName = [ICMessageDatabase getUserNameWithEId:obj];
            }
            reguleStr = [reguleStr stringByReplacingOccurrencesOfString:orgstr withString:nName];
        }];
    }
    
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result;
        
        if (reguleStr) {
            result = [db executeUpdateWithFormat:@"UPDATE IC_Group SET lastMsgTime = %ld,lastMsgTxt = %@, lastMsgId = %@,userId = %@ WHERE gId = %@;",lastMsgTime,reguleStr,lastMsgId,changeId,gId];
            
        } else {
            result = [db executeUpdateWithFormat:@"UPDATE IC_Group SET lastMsgTime = %ld, lastMsgId = %@, userId = %@ WHERE gId = %@;",lastMsgTime,lastMsgId,changeId,gId];
        }
        if (!result) NSLog(@"updateGroupLastMessage error");
    }];
}

// chage group localType
+ (void)changeGroupLocalType:(BOOL)localType
                     GroupId:(NSString *)gId
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"UPDATE IC_Group SET localType = %d WHERE gId = %@;",localType,gId];
        if (!result) NSLog(@"changeGroupLocalType error");
    }];
}

+ (BOOL)isGroupExisted:(NSString *)gId
{
    __block BOOL isExisted = YES;
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet *set   = [db executeQueryWithFormat:@"SELECT COUNT(*) FROM IC_Group WHERE gId = %@;",gId];
        int count          = 0;
        while ([set next]) {
            count          = [set intForColumnIndex:0];
        }
        if (count > 0) {
            isExisted      = YES;
        } else {
            isExisted      = NO;
        }
    }];
    return isExisted;
}

// the max messageId
+ (NSInteger)maxMessageId:(NSString *)gId
{
    __block NSInteger messageId = 0;
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet *set  = [db executeQueryWithFormat:@"SELECT * FROM IC_Message WHERE mto = %@ ORDER BY time DESC LIMIT 1",gId];
        while ([set next]) {
            NSString *mId = [set stringForColumn:@"messageId"];
            messageId     = [mId integerValue];
        }
    }];
    return messageId;
}

+ (void)messageBackAction:(NSString *)gId
                messageId:(NSString *)msgId
              lastMsgTime:(NSInteger)lastMsgTime
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        NSString *userName = @"";
        NSString *mFrom;
        //        NSInteger lastTime = 0;
        FMResultSet *set = [db executeQueryWithFormat:@"SELECT * FROM IC_Message WHERE mto = %@ AND messageId = %@;",gId,msgId];
        while ([set next]) {
            mFrom = [set stringForColumn:@"mfrom"];
            //            lastTime = [set longForColumn:@"time"];
        }
        // 优先级 为昵称 －昵称取不到则使用名字
        FMResultSet *NicknameSet = [db executeQueryWithFormat:@"SELECT nName FROM ic_group_employee WHERE eId = %@;",mFrom];
        while ([NicknameSet next]) {
            userName          = [NicknameSet stringForColumn:@"nName"];
        }
        
        if ([ICTools stringEmpty:userName]) {
            FMResultSet *nameSet = [db executeQueryWithFormat:@"SELECT eName FROM ic_employee WHERE eId = %@;",mFrom];
            while ([nameSet next]) {
                userName          = [nameSet stringForColumn:@"eName"];
            }
        }
        NSString *messageTxt = [NSString stringWithFormat:@"%@撤回了一条消息",userName];
        if ([mFrom isEqualToString:[ICUser currentUser].eId]) {
            messageTxt        = @"你撤回了一条消息";
        }
        BOOL result = [db executeUpdateWithFormat:@"UPDATE IC_Message SET type = %@,txt = %@,status = %d WHERE mto = %@ AND messageId = %@;",TypeSystem,messageTxt,ICMessageStatus_read,gId,msgId];
        if (!result) NSLog(@"messageBackAction error");
        
        BOOL lastMsgresult = [db executeUpdateWithFormat:@"UPDATE IC_Group SET lastMsgTime = %ld,lastMsgTxt = %@, userId = %@ WHERE gId = %@;",lastMsgTime,messageTxt,mFrom,gId];
        if (!lastMsgresult) NSLog(@"UPDATE  IC_Group SET lastMsgTime error");
        
    }];
}

+ (void)messageDidRead:(NSString *)gId
                 msgId:(NSString *)msgId
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result     = [db executeUpdateWithFormat:@"UPDATE IC_Message SET status = %d WHERE mto = %@ AND messageId = %@;",ICMessageStatus_read,gId,msgId];
        if (!result) NSLog(@"messageDidRead error");
    }];
}

+ (void)kickUserAction:(NSString *)gId
            messageTxt:(NSString *)messageTxt
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result  = [db executeUpdateWithFormat:@"DELETE FROM ic_group_employee WHERE gId = %@ AND eId = %@;",gId,[ICUser currentUser].eId];
        if (!result) NSLog(@"kickUserAction error");
        
        BOOL success = [db executeUpdateWithFormat:@"UPDATE IC_Group SET valid =  0 WHERE gId = %@;",gId];
        if (!success) NSLog(@"kickUserAction UPDATE IC_Group error");
    }];
}

// group type
+ (NSString *)groupTypeWithGroupId:(NSString *)gId
{
    __block NSString *gType;
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet *set = [db executeQueryWithFormat:@"SELECT * FROM IC_Group WHERE gId = %@;",gId];
        while ([set next]) {
            gType        = [set stringForColumn:@"gType"];
        }
    }];
    return gType;
}

// did have unreadCount
+ (BOOL)isRedCircleAppear
{
    if ([ICSyncManager sharedInstance].isSynchronizing) {
        return YES;
    }
    __block BOOL isAppear = NO;
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet *set = [db executeQueryWithFormat:@"SELECT * FROM IC_Group WHERE busy = %d",0];
        while ([set next]) {
            int unReadCount = [set intForColumn:@"unreadCount"];
            if (unReadCount > 0) {
                isAppear    = YES;
                break ;
            }
        }
    }];
    return isAppear;
}

+ (void)updateMessageStatus:(NSString *)messageId
                        gId:(NSString *)gId
                     status:(ICMessageStatus)status
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) { // PDATE IC_Message SET mediaPath = %@ WHERE
        BOOL result = [db executeUpdateWithFormat:@"UPDATE IC_Message SET status = %d WHERE mto = %@ AND messageId = %@;",status,gId,messageId];
        if (!result) NSLog(@"updateVoiceMessageStatus error");
    }];
}

// search message
+ (void)searchMessage:(NSString *)gId
            searchTxt:(NSString *)searchTxt
               finish:(finishedBlock)finish
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        NSString *sql = [NSString stringWithFormat:@"SELECT * FROM IC_Message WHERE (mto = '%@' AND (type = '%@' OR type = '%@') AND (status = %d OR status = %d) AND txt like '%%%@%%' ) ORDER BY time DESC;",gId,TypeText,TypeAtText,ICMessageStatus_read,ICMessageStatus_unRead,searchTxt];
        FMResultSet *set    = [db executeQuery:sql];
        NSMutableArray *arr = [NSMutableArray array];
        while ([set next]) {
            ICSearchMessage *searchMessage = [[ICSearchMessage alloc] init];
            searchMessage.eId              = [set stringForColumn:@"mfrom"];
            searchMessage.messageTxt       = [set stringForColumn:@"txt"];
            searchMessage.date             = [set longForColumn:@"time"];
            searchMessage.messageId        = [set stringForColumn:@"messageId"];
            FMResultSet *nameSet           = [db executeQueryWithFormat:@"SELECT * FROM ic_employee WHERE eId = %@;",searchMessage.eId];
            searchMessage.key = searchTxt;
            while ([nameSet next]) {
                searchMessage.eName        = [nameSet stringForColumn:@"eName"];
                searchMessage.photoId      = [nameSet stringForColumn:@"photoId"];
                if (searchMessage.eName) {
                    break;
                }
            }
            [arr addObject:searchMessage];
        }
        finish(arr,nil);
    }];
}

// get all message
+ (void)selectAllMessage:(NSString *)gId
                  finish:(finishedBlock)finish
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet *set = [db executeQueryWithFormat:@"SELECT * FROM IC_Message WHERE mto = %@ ORDER BY time DESC;",gId];
        NSInteger lastTime = 0;
        NSMutableArray *mutableArray = [NSMutableArray array];
        
        while ([set next]) {
            ICMessageFrame *messageF     = [[ICMessageFrame alloc] init];
            ICMessageModel *messageModel = [[ICMessageModel alloc] init];
            ICMessage *message           = [[ICMessage alloc] init];
            
            message.messageId = [set stringForColumn:@"messageId"];
            message.from      = [set stringForColumn:@"mfrom"];
            message.to        = [set stringForColumn:@"mto"];
            message.type      = [set stringForColumn:@"type"];
            message.content   = [set stringForColumn:@"txt"];
            message.fileKey   = [set stringForColumn:@"fileKey"];
            messageModel.isSender  = [set boolForColumn:@"isSender"];
            message.deliveryState  = [set intForColumn:@"deliveryState"];
            message.status    = [set intForColumn:@"status"];
            messageModel.mediaPath = [set stringForColumn:@"mediaPath"];
            message.date           = [set longForColumn:@"time"];
            message.lnk            = [set stringForColumn:@"lnk"];
            
            messageModel.message         = message;
            messageF.model               = messageModel;
            if (message.deliveryState == ICMessageDeliveryState_Delivering) { // 对正在发送的重新处理
                message.deliveryState = ICMessageDeliveryState_Failure;
            }
            NSDateFormatter *dateFormater = [[NSDateFormatter alloc] init];
            [dateFormater setDateFormat:@"yyyy-MM-dd"];
            if (lastTime > 0) {
                NSDate *date1 = [NSDate dateWithTimeIntervalSince1970:lastTime/1000];
                NSDate *date2 = [NSDate dateWithTimeIntervalSince1970:message.date/1000];
                BOOL isSame = [NSDate isSameDay:date1 date2:date2];
                if (!isSame) {
                    NSString *datestr = [dateFormater stringFromDate:date1];
                    ICMessageFrame *timeF = [ICMessageHelper createTimeMessageFrame:TypeSystem
                                                                            content:datestr
                                                                               path:nil
                                                                               from:nil
                                                                                 to:message.to
                                                                            fileKey:nil
                                                                           isSender:NO
                                                           receivedSenderByYourself:NO];
                    [mutableArray addObject:timeF];
                }
            }
            lastTime = message.date;
            
            [mutableArray addObject:messageF];
            
            if ([message.messageId isEqualToString:@"1"]) {// 创建的时间
                NSString *datestr = [dateFormater stringFromDate:[NSDate dateWithTimeIntervalSince1970:message.date/1000]];
                ICMessageFrame *timeF = [ICMessageHelper createTimeMessageFrame:TypeSystem
                                                                        content:datestr
                                                                           path:nil
                                                                           from:nil
                                                                             to:message.to
                                                                        fileKey:nil
                                                                       isSender:NO
                                                       receivedSenderByYourself:NO];
                [mutableArray addObject:timeF];
            }
        }
        
        if (mutableArray.count > 0) { // 有数据
            NSArray *reverseArry = [[mutableArray reverseObjectEnumerator] allObjects];
            finish(reverseArry,nil);
        } else {    // 没加载到数据
            finish(nil,nil);
        }
    }];
}

// group name
+ (NSString *)getGroupName:(NSString *)gId
{
    __block NSString *name ;
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet *set = [db executeQueryWithFormat:@"SELECT * FROM IC_Group WHERE  gId = %@;",gId];
        while ([set next]) {
            name = [set stringForColumn:@"gName"];
        }
    }];
    return name;
}

+ (NSInteger)getGroupUserCount:(NSString *)gId {
    __block NSInteger count = 0;
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase * _Nonnull db, BOOL * _Nonnull rollback) {
        NSString *sql = [NSString stringWithFormat:@"select count(*) from ic_group_employee where  gId = '%@';",gId];
        count = [db intForQuery:sql];
    }];
    return count;
}

+ (void)fileMessageWithGid:(NSString *)gId
                    finish:(finishedBlock)finish
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet *set = [db executeQueryWithFormat:@"SELECT * FROM IC_Message WHERE type = %@ AND mto = %@;",TypeFile,gId];
        NSMutableArray *mutableArray = [NSMutableArray array];
        while ([set next]) {
            ICMessage *message           = [[ICMessage alloc] init];
            message.messageId = [set stringForColumn:@"messageId"];
            message.from      = [set stringForColumn:@"mfrom"];
            message.to        = [set stringForColumn:@"mto"];
            message.content   = [set stringForColumn:@"txt"];
            message.fileKey   = [set stringForColumn:@"fileKey"];
            message.deliveryState  = [set intForColumn:@"deliveryState"];
            message.status    = [set intForColumn:@"status"];
            message.date           = [set longForColumn:@"time"];
            message.lnk            = [set stringForColumn:@"lnk"];
            [mutableArray addObject:message];
        }
        finish(mutableArray,nil);
    }];
}

// 组的lastUpdate
+ (NSNumber *)groupInfoLastUpdate:(NSString *)gId
{
    __block NSInteger lastUpdate = 0;
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet *set = [db executeQueryWithFormat:@"SELECT lastUpdate FROM IC_Group WHERE gId = %@;",gId];
        while ([set next]) {
            lastUpdate = [set longForColumn:@"lastUpdate"];
        }
    }];
    return lastUpdate == 0 ? @0 : [NSNumber numberWithInteger:lastUpdate];
}

+ (void)setGroupInfoLastUpdate:(NSString *)gId
                    lastUpdate:(NSNumber *)lastUpdate
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"UPDATE IC_Group SET lastUpdate = %ld WHERE gId = %@;",lastUpdate.integerValue,gId];
        if (!result) NSLog(@"setGroupInfoLastUpdate error");
    }];
}



// save gtasks
+ (void)saveGtasks:(ICGtasksModel *)gtasks {
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"INSERT OR REPLACE INTO ic_todo_message(appId,msgId,title,summary,confirmurl,rejecturl,openurl,status,hide,time)VALUES(%@,%@,%@,%@,%@,%@,%@,%@,%ld,%ld);",gtasks.fr,gtasks.msgId,gtasks.title,gtasks.summary,gtasks.confirmurl,gtasks.rejecturl,gtasks.openurl,gtasks.status,gtasks.hide,(long)gtasks.st];
        if (!result) NSLog(@"save ic_todo_message error");
    }];
}

+ (void)getGtasksListWithGroup:(NSString *)group
                        status:(NSString *)status
                         msgId:(NSInteger)msgId
                         Count:(int)count
                        finish:(finishedBlock)finish {
    NSString *sql = @"";
    if (![status isEqualToString:@"3"]) {
        sql = [NSString stringWithFormat:@"SELECT * FROM IC_Message WHERE mto = '%@' AND status = %@  AND messageId > %ld ORDER BY messageId LIMIT %d",group,status,msgId,count];
    } else {
        sql = [NSString stringWithFormat:@"SELECT * FROM IC_Message WHERE mto = '%@' AND messageId > %ld ORDER BY messageId LIMIT %d", group,msgId,count];
    }
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet *set = [db executeQueryWithFormat:sql];
        NSMutableArray *mutableArr = [NSMutableArray array];
        while ([set next]) {
            ICMessage *message           = [[ICMessage alloc] init];
            
            message.messageId = [set stringForColumn:@"messageId"];
            message.from      = [set stringForColumn:@"mfrom"];
            message.to        = [set stringForColumn:@"mto"];
            message.type      = [set stringForColumn:@"type"];
            message.content   = [set stringForColumn:@"txt"];
            message.fileKey   = [set stringForColumn:@"fileKey"];
            message.deliveryState  = [set intForColumn:@"deliveryState"];
            message.status    = [set boolForColumn:@"status"];
            message.date           = [set longForColumn:@"time"];
            message.lnk            = [set stringForColumn:@"lnk"];
            
            [mutableArr addObject:message];
        }
        finish(mutableArr,nil);
    }];
    
}

+ (void)setGtasksMsgId:(NSString *)msgId
                Status:(NSString *)status {
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"UPDATE ic_todo_message SET status = %@ WHERE msgId = %@",status, msgId];
        if (!result) NSLog(@"UPDATE ic_todo_message error");
    }];
}

+ (void)selectGroupsWithgType:(NSString *)gType
                    localType:(NSInteger )localType
                       finish:(finishedBlock)finish
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet * set = [db executeQueryWithFormat:@"SELECT * FROM IC_Group WHERE (gType = %@ or gType = 11) AND valid = 1 AND localType = %ld ORDER BY lastMsgTime DESC;",gType, localType]; // ORDER BY lastMsgTime DESC
        
        if (nil == set) {
            NSLog(@"no group");
            finish(nil,nil);
            return ;
        }
        NSMutableArray *mutableArr = [NSMutableArray array];
        while ([set next]) {
            ICGroup *group   = [[ICGroup alloc] init];
            group.gId        = [set stringForColumn:@"gId"];
            group.gName      = [set stringForColumn:@"gName"];
            group.gType      = [set stringForColumn:@"gType"];
            group.master     = [set intForColumn:@"master"];
            group.unReadCount   = [set intForColumn:@"unreadCount"];
            group.isTop         = [set intForColumn:@"isTop"];
            group.lastMsgTime = [set longForColumn:@"lastMsgTime"];
            group.photoId     = [set stringForColumn:@"photoId"];
            group.valid       = [set intForColumn:@"valid"];
            [mutableArr addObject:group];
        }
        finish(mutableArr,nil);
    }];
}

// with gtype to search groups
+ (void)searchGroupsWithSearchText:(NSString *)text
                             gType:(NSString *)gType
                         localType:(NSInteger )localType
                            finish:(finishedBlock)finish
{
    NSString *sql=[NSString stringWithFormat:@"SELECT * FROM IC_Group WHERE localType = %ld AND (gType = 2 or gType = 11) AND gName like '%%%@%%' ORDER BY lastMsgTime DESC;",(long)localType,text];
    
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet *set = [db executeQuery:sql];
        NSMutableArray *mutableArr = [NSMutableArray array];
        while ([set next]) {
            ICGroup *group   = [[ICGroup alloc] init];
            group.gId        = [set stringForColumn:@"gId"];
            group.gName      = [set stringForColumn:@"gName"];
            group.gType      = [set stringForColumn:@"gType"];
            group.master     = [set intForColumn:@"master"];
            group.unReadCount   = [set intForColumn:@"unreadCount"];
            group.isTop         = [set intForColumn:@"isTop"];
            group.lastMsgTime = [set longForColumn:@"lastMsgTime"];
            group.photoId     = [set stringForColumn:@"photoId"];
            group.valid       = [set intForColumn:@"valid"];
            [mutableArr addObject:group];
        }
        
        finish(mutableArr,nil);
    }];
}

+ (void)saveDrafts:(NSString *)gId
             draft:(NSString *)draft
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"INSERT OR REPLACE INTO IC_Drafts (gId,draft) VALUES (%@,%@);",gId,draft];
        if (!result) NSLog(@"saveDrafts error");
    }];
}

// 如果为空，则表示不存在
+ (NSString *)getDrafts:(NSString *)gId
{
    __block NSString *draft;
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet *set = [db executeQueryWithFormat:@"SELECT draft FROM IC_Drafts WHERE gId = %@;",gId];
        while ([set next]) {
            draft = [set stringForColumn:@"draft"];
        }
    }];
    return draft;
}

+ (void)deleteDrafts:(NSString *)gId
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"DELETE FROM IC_Drafts WHERE gId = %@;",gId];
        if (!result) NSLog(@"deleteDrafts error");
    }];
}


+ (void)deleteAllMessages
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"DELETE FROM IC_Message;"];
        [db executeUpdateWithFormat:@"DELETE FROM ic_direct_message"];
        if (!result) NSLog(@"deleteAllMessages error");
    }];
}

//+ (void)deleteAllMessages2:(finishedBlock)finish
//{
//    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
//        BOOL result = [db executeUpdateWithFormat:@"DELETE FROM IC_Message;"];
//        [db executeUpdateWithFormat:@"DELETE FROM ic_direct_message"];
//        if (!result) NSLog(@"deleteAllMessages error");
//
//        if (finish) {
//            finish(nil,nil);
//        }
//    }];
//}

// save deliver
+ (void)saveApplication:(ICApplicationModel *)app {
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"INSERT OR REPLACE INTO ic_app(appId,appName,appClassId,appClassName,appType,lightAppURL,iosURL,iosScheme,webURL,weight,photoId,appvalid)VALUES(%@,%@,%d,%@,%d,%@,%@,%@,%@,%ld,%@,%d);",app.appId,app.appName,app.appClassId,app.appClassName,app.appType,app.lightAppURL,app.URL,app.Scheme,app.webURL,(long)app.weight,app.photoId,app.appvalid];
        if (!result) NSLog(@"save ICApplication error");
    }];
}

+ (void)updateApplicationappClassId:(ICApplicationModel *)app {
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"UPDATE ic_app SET appClassId = %d WHERE appId = %@",app.appClassId,app.appId];
        if (!result) NSLog(@"updateGroupNameWithGroupId error");
    }];
    
}

+ (void)updateApplicationSubs:(ICApplicationModel *)app {
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"UPDATE ic_app SET subs = %ld WHERE appId = %@",(long)app.subs,app.appId];
        if (!result) NSLog(@"updateGroupNameWithGroupId error");
    }];
    
}

+ (void)selectApplicationsWithSubs:(NSInteger)Subs
                          SubsType:(NSInteger)subsType
                            finish:(finishedBlock)finish {
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        NSString *sql;
        sql = [NSString stringWithFormat:@"select * from ic_app where weight >= 0 and subs = %ld and subsType = %ld order by weight DESC;", Subs, subsType];
        FMResultSet *set = [db executeQuery:sql];
        NSMutableArray *mutableArr = [[NSMutableArray alloc] init];
        while ([set next]) {
            ICApplicationModel *model = [[ICApplicationModel alloc] init];
            model.appId = [set stringForColumn:@"appId"];
            model.appName = [set stringForColumn:@"appName"];
            model.photoId = [set stringForColumn:@"photoId"];
            model.appType = [set intForColumn:@"appType"];
            model.subsType = [set intForColumn:@"subsType"];
            model.lightAppURL = [set stringForColumn:@"lightAppURL"];
            model.URL = [set stringForColumn:@"URL"];
            model.Scheme = [set stringForColumn:@"Scheme"];
            model.webURL = [set stringForColumn:@"webURL"];
            model.valid  = [set intForColumn:@"valid"];
            model.subs = [set intForColumn:@"subs"];
            model.weight = [set intForColumn:@"weight"];
            model.appDesc = [set stringForColumn:@"appDesc"];
            [mutableArr addObject:model];
        }
        finish(mutableArr,nil);
    }];
}

// 获取具体某个应用
+ (void)getApplicationWithAppId:(NSString *)appId
                         finish:(finishedBlock)finish {
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        NSString *sql;
        sql = [NSString stringWithFormat:@"select * from ic_app where appId = '%@';", appId];
        FMResultSet *set = [db executeQuery:sql];
        NSMutableArray *mutableArr = [[NSMutableArray alloc] init];
        while ([set next]) {
            ICApplicationModel *model = [[ICApplicationModel alloc] init];
            model.appId = [set stringForColumn:@"appId"];
            model.appName = [set stringForColumn:@"appName"];
            model.photoId = [set stringForColumn:@"photoId"];
            model.appType = [set intForColumn:@"appType"];
            model.subsType = [set intForColumn:@"subsType"];
            model.lightAppURL = [set stringForColumn:@"lightAppURL"];
            model.URL = [set stringForColumn:@"URL"];
            model.Scheme = [set stringForColumn:@"Scheme"];
            model.webURL = [set stringForColumn:@"webURL"];
            model.valid  = [set intForColumn:@"valid"];
            model.subs = [set intForColumn:@"subs"];
            model.weight = [set intForColumn:@"weight"];
            model.appDesc = [set stringForColumn:@"appDesc"];
            [mutableArr addObject:model];
        }
        finish(mutableArr,nil);
    }];
}

+ (void)getBaseInfoWithgId:(NSString *)gId
                    finish:(finishedBlock)finish {
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        NSString *sql = [NSString stringWithFormat:@"select id,name,photoId from ic_v_info where id = '%@'",gId];
        FMResultSet *set = [db executeQuery:sql];
        NSMutableArray *mutableArr = [NSMutableArray array];
        while ([set next]) {// 暂时使用user类处理
            ICUser *user   = [[ICUser alloc] init];
            user.eId       = [set stringForColumn:@"id"];
            user.eName     = [set stringForColumn:@"name"];
            user.photoId   = [set stringForColumn:@"photoId"];
            [mutableArr addObject:user];
        }
        finish(mutableArr,nil);
    }];
}




// 保存轻应用
+ (void)saveJobapplication:(ICApplicationModel *)app
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"INSERT OR REPLACE INTO ic_app(appId,appName,appClassId,appClassName,appType,lightAppURL,iosURL,iosScheme,webURL,weight,photoId,appvalid)VALUES(%@,%@,%d,%@,%d,%@,%@,%@,%@,%ld,%@,%d);",app.appId,app.appName,app.appClassId,app.appClassName,app.appType,app.lightAppURL,app.URL,app.Scheme,app.webURL,(long)app.weight,app.photoId,app.appvalid];
        if (!result) NSLog(@"saveJobapplication save ICApplication error");
        else NSLog(@"saveJobapplication save ICApplication");
    }];
    
}


// 更新轻应用
+ (void)updateJobapplication:(ICApplicationModel *)app
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
//        BOOL result = [db executeUpdateWithFormat:@"INSERT OR REPLACE INTO ic_app(appId,appName,appType,lightAppURL,iosURL,iosScheme,webURL,photoId,appvalid)VALUES(%@,%@,%d,%@,%@,%@,%@,%@,%d);",app.appId,app.appName,app.appType,app.lightAppURL,app.URL,app.Scheme,app.webURL,app.photoId,app.appvalid];
        
        BOOL result = [db executeUpdateWithFormat:@"UPDATE ic_app SET appName = %@, appType = %d,lightAppURL = %@,iosURL = %@,iosScheme = %@,webURL = %@,photoId = %@,appvalid=%d WHERE appId = %@;",app.appName,app.appType,app.lightAppURL,app.URL,app.Scheme,app.webURL,app.photoId,app.appvalid,app.appId];
        
        if (!result)
        {
            BOOL result1 = [db executeUpdateWithFormat:@"INSERT OR REPLACE INTO ic_app(appId,appName,appClassId,appClassName,appType,lightAppURL,iosURL,iosScheme,webURL,weight,photoId,appvalid)VALUES(%@,%@,%d,%@,%d,%@,%@,%@,%@,%ld,%@,%d);",app.appId,app.appName,app.appClassId,app.appClassName,app.appType,app.lightAppURL,app.URL,app.Scheme,app.webURL,(long)app.weight,app.photoId,app.appvalid];
            if (!result1) NSLog(@"saveJobapplication save ICApplication error");
            NSLog(@"updateJobapplication save ICApplication error");
        }
        else
        {
            NSLog(@"updateJobapplication save ICApplication");
        }

    }];
}
// 取appClassId应用列表
+ (void)selectJobapplicationsWithappvalid:(NSInteger)appvalid
                               appClassId:(NSInteger)appClassId
                                   finish:(finishedBlock)finish
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        NSString *sql;
        if( appClassId == -1){
            sql = [NSString stringWithFormat:@"select * from ic_app where appvalid = %d;",(int)appvalid];
        }else{
        sql = [NSString stringWithFormat:@"select * from ic_app where appvalid = %d and appClassId = %d ORDER BY weight;",(int)appvalid,(int)appClassId];
        }
        FMResultSet *set = [db executeQuery:sql];
        NSMutableArray *mutableArr = [[NSMutableArray alloc] init];
        while ([set next]) {
            ICApplicationModel *model = [[ICApplicationModel alloc] init];
            model.appId = [set stringForColumn:@"appId"];
            model.appName = [set stringForColumn:@"appName"];
            model.appClassId = [set intForColumn:@"appClassId"];
            model.appClassName = [set stringForColumn:@"appClassName"];
            model.appType = [set intForColumn:@"appType"];
            model.lightAppURL = [set stringForColumn:@"lightAppURL"];
            model.URL = [set stringForColumn:@"iosURL"];
            model.Scheme = [set stringForColumn:@"iosScheme"];
            model.webURL = [set stringForColumn:@"webURL"];
            model.photoId = [set stringForColumn:@"photoId"];
            model.weight = [set intForColumn:@"weight"];
            [mutableArr addObject:model];
        }
        finish(mutableArr,nil);
    }];

}

// 获取不同的数据 列出不同（distinct）的值
+ (void)selectJobapplicationsappClassIdWithappvalid:(int)appvalid
                                             finish:(finishedBlock)finish
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        NSString *sql;
        sql = [NSString stringWithFormat:@"select distinct appClassId , appClassName from ic_app where appvalid = %d;",appvalid];
        FMResultSet *set = [db executeQuery:sql];
        NSMutableArray *mutableArr = [[NSMutableArray alloc] init];
        
        while ([set next]) {
            ICApplicationModel *model = [[ICApplicationModel alloc] init];
            model.appClassId = [set intForColumn:@"appClassId"];
            model.appClassName = [set stringForColumn:@"appClassName"];
            [mutableArr addObject:model];
        }
        for(NSInteger i = 0; i< mutableArr.count ; i++)
        {
            ICApplicationModel *model = mutableArr[i];
            //排序
            if(model.appClassId == -1)
            {
                [mutableArr exchangeObjectAtIndex:i withObjectAtIndex:mutableArr.count-1];
            }
        }
        
        //添加了全部应用
        ICApplicationModel * model = [mutableArr lastObject];
        if(model.appClassId != -1){
            ICApplicationModel *appmodel = [[ICApplicationModel alloc] init];
            appmodel.appClassId = -1;
            appmodel.appClassName = @"全部应用";
            [mutableArr addObject:appmodel];
        }
//        NSUserDefaults * jobDefaults = [NSUserDefaults standardUserDefaults];
//        NSArray* jobappClassList = [jobDefaults objectForKey:@"jobAppClassList"];

        
        
        finish(mutableArr,nil);
    }];
}


// 更新app归属
+ (void)updateJobapplicationWeight:(ICApplicationModel *)app{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"UPDATE ic_app SET Weight = %ld WHERE appId = %@;",(long)app.weight,app.appId];
        if (!result) NSLog(@"updateGroupNameWithGroupId error");
    }];
}

// 更新app归属
+ (void)updateJobapplicationappClassId:(ICApplicationModel *)app{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"UPDATE ic_app SET appClassId = %d,appClassName = %@ WHERE appId = %@;",app.appClassId,app.appClassName,app.appId];
        if (!result) NSLog(@"updateGroupNameWithGroupId error");
    }];
}

// 更新app appvalid
+ (void)updateJobapplicationAppvalid:(int)appvalid
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"UPDATE ic_app SET appvalid = %d where appClassId < 100;",appvalid];
        if (!result) NSLog(@"updateGroupNameWithGroupId error");
    }];
}


// 更新app appvalid
+ (void)updateJobapplicationAppvalid:(int)appvalid  withappId:(NSString *)appId
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdateWithFormat:@"UPDATE ic_app SET appvalid = %d where appId = %@;",appvalid,appId];
        if (!result) NSLog(@"updateGroupNameWithGroupId error");
    }];
}



// 取自己选择应用列表
+ (void)selectJobappChoicelicationsWithappvalid:(NSInteger)appvalid
                                   finish:(finishedBlock)finish
{
    [[ICDatabaseManager shareManager].messageQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        NSString *sql;
        
        sql = [NSString stringWithFormat:@"select * from ic_app where appvalid = %d and appClassId > 99 ORDER BY weight;",(int)appvalid];
        FMResultSet *set = [db executeQuery:sql];
        NSMutableArray *mutableArr = [[NSMutableArray alloc] init];
        while ([set next]) {
            ICApplicationModel *model = [[ICApplicationModel alloc] init];
            model.appId = [set stringForColumn:@"appId"];
            model.appName = [set stringForColumn:@"appName"];
            model.appClassId = [set intForColumn:@"appClassId"];
            model.appClassName = [set stringForColumn:@"appClassName"];
            model.appType = [set intForColumn:@"appType"];
            model.lightAppURL = [set stringForColumn:@"lightAppURL"];
            model.URL = [set stringForColumn:@"iosURL"];
            model.Scheme = [set stringForColumn:@"iosScheme"];
            model.webURL = [set stringForColumn:@"webURL"];
            model.photoId = [set stringForColumn:@"photoId"];
            model.weight = [set intForColumn:@"weight"];
            [mutableArr addObject:model];
        }
        finish(mutableArr,nil);
    }];
    
}




@end
